* QCEW_proc_quarterly.do
* 2014.02.04
* Last update 2014.11.05: added 2013 data
* Processes QCEW data 

capture log close
set more off
timer clear 1
timer on 1
clear
set matsize 10000

local work "/DIRECTORY"

log using "`work'/logs/QCEW_proc_quarterly.log", replace

**********************
* QCEW
**********************
* Looping over raw files
foreach yr in 90 91 92 93 94 95 96 97 98 99 00 01 02 03 04 05 06 07 08 09 10 11 12 13 {
	forval j=1/4 {
		import excel using "`work'/data/qcew/allhlcn`yr'`j'.xls", firstrow clear
		capture drop T U V W X Y Z
		
		if `j'==1 {
			egen AvgEmployment = rowmean(JanuaryEmployment FebruaryEmployment MarchEmployment)
		}
		else if `j'==2 {
			egen AvgEmployment = rowmean(AprilEmployment MayEmployment JuneEmployment)
		}
		else if `j'==3 {
			egen AvgEmployment = rowmean(JulyEmployment AugustEmployment SeptemberEmployment)
		}
		else if `j'==4 {
			egen AvgEmployment = rowmean(OctoberEmployment NovemberEmployment DecemberEmployment)
		}
		
		* Saving tempfile
		tempfile file`yr'`j'
		save "`file`yr'`j''", replace
	}
}

* Appending
clear
foreach yr in 90 91 92 93 94 95 96 97 98 99 00 01 02 03 04 05 06 07 08 09 10 11 12 13 {
	forval j=1/4 {
		append using "`file`yr'`j''", force
	}
}

* Drop non-county records
drop if inlist(AreaType, "Nation", "State", "MSA")
* Drop government
keep if Ownership=="Private"

* Cleaning
destring Year, replace
destring Qtr, replace
destring AreaCode, replace
gen tq = yq(Year, Qtr)
format tq %tq
format Year %ty

* Reshape
keep tq AreaCode NAICS AverageWeeklyWage AvgEmployment
reshape wide AverageWeeklyWage AvgEmployment, i(tq AreaCode) j(NAICS) string

* Declaring panel
xtset AreaCode tq, quarterly

* Merging in lat and long
tostring AreaCode, generate(fips)
gen fips_length=length(fips)
replace fips = "0" + fips if fips_length==4
merge m:1 fips using "`work'/data/qcew/County_latlong.dta", keep(1 3) nogenerate

* Saving
save "`work'/data/qcew/QCEW_raw_quarterly.dta", replace








timer off 1
timer list 1
capture log close




